import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

// Demo : 示例
public class Mysql3 {
    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);

        // 1. 创建数据源 (DataSource) 对象.
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3307/yourdbname?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("111111");

        // 2. 和数据库服务器进行通信. 需要建立网络连接
        Connection connection = dataSource.getConnection();

        // 3. 构造 SQL 语句
        System.out.println("增");
        System.out.println("请输入要插入的 id: ");
        int id = scanner.nextInt();
        System.out.println("请输入要插入的 name: ");
        String name = scanner.next();
        //
        String sql = "insert into student values(?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        preparedStatement.setString(2, name);

        System.out.println("删");
        System.out.println("请输入要删除的 name: ");
        String name2 = scanner.next();
        //
        String sql2 = "delete from student where name = ?";
        PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
        preparedStatement2.setString(1, name2);

        System.out.println("改");
        System.out.println("请输入要修改的 id: ");
        int id3 = scanner.nextInt();
        System.out.println("源自谁: ");
        String name3 = scanner.next();
        //
        String sql3 = "update student set id = ? where name = ?";
        PreparedStatement preparedStatement3 = connection.prepareStatement(sql3);
        preparedStatement3.setInt(1, id3);
        preparedStatement3.setString(2, name3);

        System.out.println("查");
        String sql4 = "select * from student";
        PreparedStatement preparedStatement4 = connection.prepareStatement(sql4);
        ResultSet resultSet = preparedStatement4.executeQuery();

        // 4. 执行 SQL (把 SQL 通过网络发送到服务器)
        //    进行增, 删, 改, 甚至建表删表...... 这些操作统称为 update
        //    查, 有另一个方法 executeQuery
        int n = preparedStatement.executeUpdate();
        System.out.println("n = " + n);

        int n2 = preparedStatement2.executeUpdate();
        System.out.println("n2 = " + n2);

        int n3 = preparedStatement3.executeUpdate();
        System.out.println("n3 = " + n3);

        // 5. 关闭连接等资源.
        preparedStatement.close();
        preparedStatement2.close();
        preparedStatement3.close();
        preparedStatement4.close();
        resultSet.close();
        connection.close();
    }
}